# CamBank

from camlib import BasicPage
from time import localtime

class CamBank (BasicPage.BasicPage):
 def __init__(self, username, data, crypt=None):
  BasicPage.BasicPage.__init__(self, 'CamBank')
  self.username = username
  self.metas['title'] = 'CamBank'
  self.metas['description'] = 'CamBank, le logiciel de compte personnel bas&eacute; sur les budgets !'
  self._tree = None
  self._full_name = None
  self._id = None
  self.data = data
  self.sympass = crypt
  self.version = 4
  self.epargnes = None
 
 def symcr(self, str):
  if self.sympass:
   return "pgp_sym_encrypt('%s','%s')" % \
    (self.data.escape_string(str), self.sympass)
  else:
   return "'"+self.data.escape_string(str)+"'"
 
 def symde(self, col):
  if self.sympass:
   return "pgp_sym_decrypt(%s, '%s')" % \
    (col, self.sympass)
  else:
   return col

 def change_password(self, cur, new):
  self.data.begin()
  self.data.execute("update cb.budgets set bnom=pgp_sym_encrypt(pgp_sym_decrypt(bnom, '%s'), '%s'),bcomment=pgp_sym_encrypt(pgp_sym_decrypt(bcomment, '%s'), '%s')" % (cur, new, cur, new))
  self.data.execute("update cb.categories set cnom=pgp_sym_encrypt(pgp_sym_decrypt(cnom, '%s'), '%s')" % (cur, new))
  self.data.execute("update cb.operations set olibelle=pgp_sym_encrypt(pgp_sym_decrypt(olibelle, '%s'), '%s'),odetail=pgp_sym_encrypt(pgp_sym_decrypt(odetail, '%s'), '%s')" % (cur, new, cur, new))
  self.data.execute("alter user %s with password '%s'" % (self.username, new))
  self.data.commit()

 def get_last_import_date(self, pattern):
  return self.data.select_one('select ' \
   'to_char(last_import, \'DD/MM/YYYY\') from cb.last_import_date ' \
   'where pattern=\'%s\'' % (pattern))
 

 """ gestion des operations """

 def create_operation(self, date, detail, montant):
  self.data.execute('insert into cb.operations ' \
   '(oid, odate, olibelle, odetail, omontant) values ' \
   '(nextval(\'cb.oid_seq\'), \'%s\', %s, %s, %d)' % 
   (self.data.escape_string(date),
    self.symcr('Ajout manuel'),
    self.symcr(detail),
    montant))
  self.data.commit()

 def get_last_operation_date(self):
  return self.data.select_one('select ' \
   'to_char(max(odate)+1, \'DD/MM/YYYY\') from cb.operations')
 
 def get_pending_operations(self):
  ops = []
  for line in self.data.select_all('select oid from cb.operations o ' \
    'where obalance<>omontant or (omontant=0 and not exists ' \
    '(select aid from cb.actions a where a.oid=o.oid)) order by odate'):
   ops.append(Operation(self, line[0]))
  return ops
 
 def mask_operation(self, oid):
  op = Operation(self, oid)
  op.set_balance(op.omontant)
 

 """ gestion des categories """

 def create_category(self, nom, ordre):
  self.data.execute('update cb.categories ' \
   'set corder=corder+1 where corder >= %d' % (ordre))
  self.data.execute('insert into cb.categories ' \
   '(cnom, corder) values (%s, %d)' % \
   (self.symcr(nom), ordre))
  self.data.commit()

 def get_categories(self):
  cats = []
  for line in self.data.select_all('select cid from cb.categories ' \
    'order by corder'):
   cats.append(Category(self, line[0]))
  return cats


 """ gestion des budgets """

 def create_budget(self, nom, montant, comment, cid):
  self.data.execute('insert into cb.budgets ' \
   '(bnom, bmontant, cid, bcomment) values (%s, %d, %d, %s)' % 
   (self.symcr(nom), montant, cid,
    comment and self.symcr(comment) or 'NULL'))
  self.data.commit()

 def get_budgets(self):
  buds = []
  for line in self.data.select_all('select bid from cb.budgets order by bid'):
   buds.append(Budget(self,line[0]))
  return buds

 def get_solde(self):
  return self.data.select_one('select sum(bjauge) from cb.budgets') or 0

 def get_debts(self):
  return self.data.select_one('select sum(bjauge) from cb.budgets where bjauge<0') or 0

 def get_budgets_summary(self):
  return self.data.select_all('select c.cid, %s, b.bid, %s, %s, bjauge, bmontant, to_char(coalesce(max(adate), \'1900-01-01\'), \'Le DD/MM/YYYY a HH24:MI:SS\') from cb.budgets as b, cb.categories as c, cb.actions as a where b.cid=c.cid and b.bid=a.bid and bshow=true and amontant>0 group by c.cid, cnom, b.bid, bnom, bcomment, bjauge, bmontant, corder order by corder, b.bid' % (self.symde('cnom'), self.symde('bnom'), self.symde('bcomment')));

 """ gestion des mensualites """

 def create_salary(self, bid, montant):
  self.data.execute('insert into cb.mensualites ' \
    '(bid, mmontant) values (%d, %d)' %
    (bid, montant))
  self.data.commit()

 def get_salary(self):
  return self.data.select_all('select mid,bid,mmontant from cb.mensualites order by mid')

 def delete_salary(self, mid):
  self.data.execute('delete from cb.mensualites where mid=%d' % (mid))
  self.data.commit()

 def dispatch_salary(self, oid):
  sals = self.get_salary()
  for sal in sals:
   self.create_writing(self.get_action_ticket(), oid, sal[2], sal[1])


 """ gestion des epargnes """

 def create_epargne(self, nom, min, suppl, max, order):
  self.data.execute('insert into cb.epargnes ' \
   '(ename, emin_jauge, esuppl_jauge, emax_balance, eorder) values (%s, %d, %d, %d, %d)' 
   % (self.symcr(nom), min, suppl, max, order))
  self.data.commit()

 def get_epargnes(self):
  if self.epargnes == None:
   self.epargnes = []
   for e in self.data.select_all('select eid, %s, emin_jauge, ' \
     'esuppl_jauge, ebalance, ejauge, emax_balance, %s, eshow, eorder ' \
     'from cb.epargnes order by eorder' % 
     (self.symde('ename'), self.symde('ecomment'))):
    self.epargnes.append(Epargne(self, *e))
  return self.epargnes

 def get_epargne(self, eid):
  e = self.data.select_line('select eid, %s, emin_jauge, ' \
   'esuppl_jauge, ebalance, ejauge, emax_balance, %s, eshow, eorder ' \
   'from cb.epargnes where eid=%d' %
   (self.symde('ename'), self.symde('ecomment'), int(eid)))
  return Epargne(self, *e)

 def hide_epargne(self, eid):
  self.data.execute('update cb.epargnes set eshow=false where eid=%d' \
   % (eid))
  self.data.commit()

 def update_epargne(self, eid, nom, min, suppl, max, order):
  self.data.execute('update cb.epargnes set ename=%s, emin_jauge=%d, esuppl_jauge=%d, emax_balance=%d, eorder=%d where eid=%d' % (self.symcr(nom), min, suppl, max, order, eid))
  self.data.commit()


 """ gestion des actions """
  
 def create_writing(self, aid, oid, montant, bid, epargne=0):
  if type(bid) == type('') and bid == 'auto':
   self.epargne(aid, oid, montant)
  elif type(bid) == type('') and bid[0] == 'e':
   ep = self.get_epargne(bid[1:])
   ep.bank(oid, montant, aid, epargne)
  else:
   bud = Budget(self, int(bid))
   bud.bank(oid, montant, aid)
 
 def check_action_ticket(self, aid):
  if aid > self.data.select_one('select greatest(0, max(aid)) ' \
    'from cb.actions'):
   return self.data.select_one('select last_value from cb.aid_seq')
  else:
   return -1
 
 def get_action_ticket(self):
  return self.data.select_one("select nextval('cb.aid_seq')")
 
 def epargne(self, aid, oid, montant):
  def find_next():
   (eid, reste) = self.data.select_line('select eid, reste from (select ' \
    'eid, emin_jauge-ejauge as reste from cb.epargnes where emin_jauge-ejauge' \
    ' > 0 and eshow=true order by eorder) as a limit 1') or (None, None)
   if reste and reste > 0:
    return (eid, reste)
   return (0, 0)

  def distrib_epargne(eid, combien, aid, oid):
   self.create_writing(aid, oid, combien, 'e%d' % (eid), 1)

  def dispatch(combien, aid, oid):
   init = combien
   es = self.data.select_all('select eid, round(1.0*esuppl_jauge/' \
    '(select sum(esuppl_jauge) from cb.epargnes ' \
    'where ebalance < emax_balance and eshow=true)), ' \
    'emax_balance from cb.epargnes where ebalance < emax_balance and ' \
    'eshow=true')
   for (eid, ratio, max) in es[0:len(es)-2]:
    allowable = min(init*ratio, max)
    distrib_epargne(eid, allowable, aid, oid)
    aid = self.get_action_ticket()
    combien -= allowable
   distrib_epargne(eid, combien, aid, oid)
  
  while montant > 0:
   (eid, reste) = find_next()
   if eid == 0:
    break
   distrib_epargne(eid, min(montant, reste), aid, oid)
   montant -= min(montant, reste)
   aid = self.get_action_ticket()
  if montant > 0:
   dispatch(montant, aid, oid)

""" dans certains contextes, permet de melanger les budgets et les epargnes """

class Boite:
 def __init__(self, cb, type, id, nom, jauge, comment, show):
  self.cb = cb
  self.type = type
  self.id = id
  self.nom = nom
  self.jauge = jauge
  self.comment = comment
  self.show = show

 def get_actions(self):
  actions = []
  for line in self.cb.data.select_all('select aid from cb.actions ' \
    'where bid=%d order by adate' % \
    self.id):
   actions.append(Action(self.cb, line[0]))
  return actions


""" budget : une boite où on peut mettre et retirer de l'argent """

class Budget(Boite):
 def __init__(self, cb, bid):
  self.bid = bid
  tmp = cb.data.select_line('select %s, bmontant, bjauge, %s, cid, bshow ' \
   'from cb.budgets where bid=%d' % (cb.symde('bnom'), cb.symde('bcomment'),
   bid))
  if tmp:
   (self.bnom, self.bmontant, self.bjauge, self.bcomment, self.cid, self.bshow) = tmp
   Boite.__init__(self, cb, 'budget', bid, self.bnom, self.bjauge, self.bcomment,
   self.bshow)
  else:
   raise TypeError

 def get_max_adate(self):
  return localtime(self.cb.data.select_one('select extract(epoch from max(adate)) from cb.actions ' \
   'where bid=%d and amontant > 0' % self.bid))

 def bank(self, oid, montant, aid):
  self.cb.data.begin()
  aid = self.cb.check_action_ticket(aid)
  if aid > 0:
   op = Operation(self.cb, oid)
   self.cb.data.execute('insert into cb.actions (aid, oid, bid, ' \
    'amontant) values (%d, %d, %d, %d)' % \
    (aid, oid, self.bid, montant))
   op.adjust_balance(montant)
   self.adjust_jauge(montant)
   self.cb.data.commit()
  else:
   self.cb.data.rollback()
  
 def set_nom(self, nom):
  self.bnom = nom
  self.cb.data.execute('update cb.budgets set bnom=%s where bid=%d' % \
   (self.cb.symcr(nom), self.bid))
  self.cb.data.commit()

 def set_montant(self, montant):
  self.bmontant = montant
  self.cb.data.execute('update cb.budgets set bmontant=%d where bid=%d' % \
   (montant, self.bid))
  self.cb.data.commit()

 def adjust_jauge(self, delta):
  self.bjauge += delta
  self.cb.data.execute('update cb.budgets set bjauge=bjauge+%d where bid=%d' % (delta, self.bid))
  self.cb.data.commit()

 def set_comment(self, comment):
  if comment == '':
   self.comment = None
   self.cb.data.execute('update cb.budgets set bcomment=NULL where bid=%d' % (self.bid))
  else:
   self.comment = comment
   self.cb.data.execute('update cb.budgets set bcomment=%s where bid=%d' \
    % (self.cb.symcr(comment), self.bid))
  self.cb.data.commit()

 def set_category(self, cid):
  self.cid=cid
  self.cb.data.execute('update cb.budgets set cid=%d where bid=%d' % (cid, self.bid))
  self.cb.data.commit()

 def delete(self):
  self.cb.data.execute('update cb.budgets set bshow=false where bid=%d' % (self.bid))
  self.cb.data.commit()
  try:
   self.cb.data.execute('delete from cb.budgets where bid=%d' % (self.bid))
   self.cb.data.commit()
  except:
   pass

class Action:
 def __init__(self, cb, aid):
  self.cb = cb
  self.aid = aid
  (self.aid, self.adate, self.oid, self.bid, self.amontant) = \
   self.cb.data.select_line('select aid, extract(epoch from adate), oid, bid, amontant from cb.actions where aid=%d' % (aid))
  self.adate = localtime(self.adate)

 def delete(self):
  self.cb.data.begin()
  self.cb.data.execute('update cb.operations set obalance = obalance - ' \
   '(select amontant from cb.actions where aid=%d) ' \
   'where oid=(select oid from cb.actions where aid=%d)' % (self.aid, self.aid))
  self.cb.data.execute('update cb.budgets set bjauge = bjauge - ' \
   '(select amontant from cb.actions where aid=%d) where ' \
   'bid=(select bid from cb.actions where aid=%d)' % (self.aid, self.aid))
  self.cb.data.execute('update cb.epargnes set ebalance = ebalance - ' \
   '(select amontant from cb.actions where aid=%d) where ' \
   'eid=(select bid from cb.actions where aid=%d)' % (self.aid, self.aid))
  self.cb.data.execute('delete from cb.actions where aid=%d' % (self.aid))
  self.cb.data.commit()

class Operation:
 def __init__(self, cb, oid):
  self.cb=cb
  self.oid = oid
  (self.odate, self.olibelle, self.odetail, self.omontant, \
   self.obalance) = cb.data.select_line('select extract(epoch from odate), %s, ' \
   '%s, omontant, obalance from cb.operations where oid=%d' % \
   (self.cb.symde('olibelle'), self.cb.symde('odetail'), oid))
  self.odate = localtime(self.odate)

 def set_balance(self, balance):
  self.obalance = balance
  self.cb.data.execute('update cb.operations set obalance=%d where oid=%d' % (balance, self.oid))
  self.cb.data.commit()

 def adjust_balance(self, delta):
  self.obalance += delta
  self.cb.data.execute('update cb.operations set obalance=obalance+%d where oid=%d' % \
   (delta, self.oid))
  self.cb.data.commit()
 
 def get_actions(self):
  aids = []
  for line in self.cb.data.select_all('select aid from cb.actions where oid=%d' % (self.oid)):
   aids.append(Action(self.cb, line[0]))
  return aids


class Category:
 def __init__(self, cb, cid):
  self.cb=cb
  self.cid=cid
  (self.cnom, self.corder) = cb.data.select_line('select %s, corder ' \
   'from cb.categories where cid=%d' % (cb.symde('cnom'), cid))

 def set_nom(self, nom):
  self.cnom = nom
  self.cb.data.execute('update cb.categories set cnom=%s where cid=%d' % \
   (self.cb.symcr(nom), self.cid))
  self.cb.data.commit()

 def set_order(self, order):
  if self.corder == order:
   pass
  self.cb.data.execute('update cb.categories set corder=0 where cid=%d' % (self.cid))
  self.cb.data.execute('update cb.categories set corder=corder-1 where corder > %d and corder <= %d' \
   % (self.corder, order))
  self.cb.data.execute('update cb.categories set corder=corder+1 where corder < %d and corder >= %d' \
   % (self.corder, order))
  self.cb.data.execute('update cb.categories set corder=%d where cid=%d' % (order, self.cid))
  self.corder = order
  self.cb.data.commit()

 def get_budgets(self):
  buds = []
  for line in self.cb.data.select_all('select bid from cb.budgets where ' \
    'cid=%d order by bid' % self.cid):
   buds.append(Budget(self.cb, line[0]))
  return buds
 
 def delete(self):
  try:
   self.cb.data.execute('delete from cb.categories where cid=%d' % (self.cid))
   self.cb.data.commit()
  except:
   pass

class Epargne(Boite):
 def __init__(self, cb, eid, ename, emin_jauge, esuppl_jauge, ebalance, ejauge, emax_balance, ecomment, eshow, eorder):
  Boite.__init__(self, cb, 'epargne', eid, ename, ebalance, ecomment, eshow)
  self.eid = eid
  self.ename = ename
  self.emin_jauge = emin_jauge
  self.esuppl_jauge = esuppl_jauge
  self.ebalance = ebalance
  self.ejauge = ejauge
  self.emax_balance = emax_balance
  self.ecomment = ecomment or ""
  self.eshow = eshow
  self.eorder = eorder

 def bank(self, oid, montant, aid, epargne=0):
  self.cb.data.begin()
  aid = self.cb.check_action_ticket(aid)
  if aid > 0:
   op = Operation(self.cb, oid)
   self.cb.data.execute('insert into cb.actions (aid, oid, bid, ' \
    'amontant) values (%d, %d, %d, %d)' % \
    (aid, oid, self.eid, montant))
   self.ebalance += montant
   self.cb.data.execute('update cb.epargnes set ebalance=ebalance+%d ' \
    'where eid=%d' % (montant, self.eid))
   op.adjust_balance(montant)
   if epargne:
    self.cb.data.execute('update cb.epargnes set ejauge=ejauge+%d ' \
     'where eid=%d' % (montant, self.eid))
    self.ejauge += montant
   self.cb.data.commit()
  else:
   self.cb.data.rollback()
